﻿using System;
using System.Data;
using System.Data.Common;
using DotNetCommon.Extensions;
using DBUtil.SqlSegment;
using DotNetCommon.Data;
using System.Reflection;
using System.Linq;
using DotNetCommon;
using System.Threading.Tasks;
using System.Threading;
using Microsoft.Data.Sqlite;
using DBUtil.Provider.Sqlite.SqlSegment;
using Microsoft.VisualBasic;
using System.Text;

namespace DBUtil.Provider.Sqlite;

/// <summary>
/// Sqlite操作对象
/// </summary>
public partial class SqliteAccess : DBAccess
{
    /// <inheritdoc />
    protected override DbConnection GetConnectionByConnectString() => new SqliteConnection(DBConn);

    #region 一次最大插入行数
    /// <inheritdoc />
    public override int InsertRowsMaxCountPerBatch => base.InsertRowsMaxCountPerBatch > 0 ? base.InsertRowsMaxCountPerBatch : 5000;
    #endregion

    #region 创建DataAdapter
    /// <summary>
    /// 创建DataAdapter
    /// </summary>
    protected override DataAdapter CreateAdapter(DbCommand cmd)
    {
        return new SqliteDataAdapter(cmd as SqliteCommand);
    }
    #endregion

    #region CreatePara 创建参数
    /// <summary>
    /// 创建参数
    /// </summary>
    /// <returns></returns>
    public override DbParameter CreatePara()
    {
        return new SqliteParameter();
    }
    #endregion

    #region 初始化
    private static Lazy<string> appName = new(() =>
    {
        var name = $"DBUtil-{Assembly.GetEntryAssembly().GetName().Name}";
        if (name.Length > 100) name = name.Substring(0, 100);
        return name;
    });
    /// <summary>
    /// 初始化
    /// </summary>
    /// <param name="DBConn">连接字符串</param>
    /// <param name="Settings">设置</param>
    public SqliteAccess(string DBConn, DBSetting Settings) : base(EnumDBType.Sqlite, resetConnectionString(DBConn), Settings)
    {
        //InitHooks();
    }

    /// <inheritdoc />
    public override string ParaPrefix => "@";

    private static string resetConnectionString(string connectionString)
    {
        var builder = new SqliteConnectionStringBuilder(connectionString);
        return builder.ConnectionString;
    }
    #endregion

    #region 获取当前db/schema/user的sqlseg
    public override string GetCurrentSchemaSqlSeg() => "database()";
    public override string GetCurrentDataBaseSqlSeg() => "database()";
    public override string GetCurrentUserSqlSeg(bool isPure = false) => isPure ? "reverse(substring(reverse(user()),instr(reverse(user()),'@')+1))" : "user()";
    public override string GetCurrentLoginUserSqlSeg(bool isPure = false) => isPure ? "reverse(substring(reverse(user()),instr(reverse(user()),'@')+1))" : "user()";
    public override string GetCurrentDataBaseVersionSqlSeg() => "version()";
    #endregion

    #region 将任意对象转为可拼接的sql ConvertToSqlSeg
    public override Result<string> ConvertToSqlSeg(object obj, object args = null)
    {
        if (obj is DateTimeOffset dateTimeOffset)
        {
            //sqlite 本身没有专门的日期时间类型, 可以按照统一的字符串格式存储
            var format = (args is string str && str.IsNotNullOrWhiteSpace()) ? str : Setting.DateTimeFormat;
            return Result.Ok($"'{dateTimeOffset.ToString(format)}'");
        }
        if (obj is DateTime dateTime)
        {
            //sqlite 本身没有专门的日期时间类型, 可以按照统一的字符串格式存储
            var format = (args is string str && str.IsNotNullOrWhiteSpace()) ? str : Setting.DateTimeFormat;
            return Result.Ok($"'{dateTime.ToString(format)}'");
        }
        if (obj is DateOnly dateOnly)
        {
            //sqlite 本身没有专门的日期时间类型, 可以按照统一的字符串格式存储
            var format = (args is string str && str.IsNotNullOrWhiteSpace()) ? str : Setting.DateFormat;
            return Result.Ok($"'{dateOnly.ToString(format)}'");
        }
        if (obj is TimeOnly timeOnly)
        {
            //sqlite 本身没有专门的日期时间类型, 可以按照统一的字符串格式存储
            var format = (args is string str && str.IsNotNullOrWhiteSpace()) ? str : Setting.TimeFormat;
            return Result.Ok($"'{timeOnly.ToString(format)}'");
        }
        if (obj is TimeSpan timeSpan)
        {
            //sqlite 中没有专门存储时间段的类型, 存储为字符串也不利于比较, 只能存储为数字
            //比如: TimeSpan.FromMilliseconds(1000 * 3600).TotalSeconds => 3600
            //比如: TimeSpan.FromMilliseconds(1000 * 3600+123).TotalSeconds => 3600.123
            return Result.Ok($"{timeSpan.TotalSeconds}");
        }
        var res = base.ConvertToSqlSeg(obj, args);
        if (res.Success) return res;

        //TODO 
        return Result.NotOk($"未能转换 {obj.GetType().FullName} 类型数据到sql!");
    }
    #endregion

    #region GetSqlForPageSize
    /// <summary>获得分页的查询语句
    /// </summary>
    /// <param name="selectSql">查询sql如:select id,name from person where age>10</param>
    /// <param name="orderSql">排序字句如:order by id</param>
    /// <param name="pageSize">页面大小,如:10</param>
    /// <param name="pageIndex">页面索引从1开始,如:1</param>
    /// <returns>返回示例:select id,name from person where age>10 order by id limit 0,10 </returns>
    public override string GetSqlForPageSize(string selectSql, string orderSql, int pageSize, int pageIndex)
    {
        string sql = string.Format("{0} {1} limit {2},{3}", selectSql, orderSql, (pageIndex - 1) * pageSize, pageSize);
        return sql;
    }
    #endregion

    #region Is系列: 判断表/视图/列/存储过程是否存在
    private string IsProcedureExistSql(string procPureName, string schemaPureName = null)
    {
        string sql = $"show PROCEDURE status where name={ProtectStringToSeg(procPureName)} and `type` = 'PROCEDURE'";
        if (schemaPureName.IsNotNullOrWhiteSpace()) sql += $" and Db={ProtectStringToSeg(schemaPureName)}";
        else sql += $" and Db={GetCurrentSchemaSqlSeg()}";
        return sql;
    }
    public override async Task<bool> IsProcedureExistAsync(string procPureName, string schemaPureName = null, CancellationToken cancellationToken = default)
    {
        AssertUtil.NotNullOrWhiteSpace(procPureName);
        var sql = IsProcedureExistSql(procPureName, schemaPureName);
        var r = await SelectScalarAsync<string>(sql, cancellationToken);
        return r.IsNotNullOrWhiteSpace();
    }

    private string IsTriggerExistSql(string triggerPureName, string schemaPureName = null)
    {
        var sql = $"select count(1) from information_schema.TRIGGERS t where t.TRIGGER_NAME={ProtectStringToSeg(triggerPureName)}";
        if (schemaPureName.IsNotNullOrWhiteSpace()) sql += $" and t.TRIGGER_SCHEMA = {ProtectStringToSeg(schemaPureName)}";
        else sql += $" and TRIGGER_SCHEMA={GetCurrentSchemaSqlSeg()}";
        return sql;
    }
    public override async Task<bool> IsTriggerExistAsync(string triggerPureName, string schemaPureName = null, CancellationToken cancellationToken = default)
    {
        AssertUtil.NotNullOrWhiteSpace(triggerPureName);
        var sql = IsTriggerExistSql(triggerPureName, schemaPureName);
        return await SelectScalarAsync<int>(sql, cancellationToken) > 0;
    }
    #endregion

    #region 数据库管理对象 Manage
    private DBManage _dbmanage = null;
    /// <summary>
    /// 数据库管理对象
    /// </summary>
    public override DBManage Manage => _dbmanage ??= new SqliteManage(this);
    #endregion

    #region 运算符
    /// <summary>
    /// 数据库日期时间运算符
    /// </summary>
    protected override DateTimeSqlSegment GetDateTimeSqlSegment() => new SqliteDateTimeSqlSegment(this);
    protected override StringSqlSegment GetStringSqlSegment() => new SqliteStringSqlSegment(this);
    protected override ConvertSqlSegment GetConvertSqlSegment() => new SqliteConvertSqlSegment(this);
    protected override OtherSqlSegment GetOtherSqlSegment() => new SqliteOtherSqlSegment(this);
    #endregion

    #region 使用SqlBulkCopy批量插入数据
    public override void BulkCopy(DataTable dt, string tableName = null, int timeoutSeconds = 60 * 30, int notifyAfter = 0, Func<long, bool> callBack = null)
    {
        //sqllite本身没有 使用 insert 实现
        throw new NotImplementedException();
    }
    #endregion

    #region 获取刚插入的自增id的Sql语句
    public override string GetLastInsertedIdSeg(int len = 1)
    {
        if (len == 1) return $"last_insert_rowid()";
        return $"last_insert_rowid()";
    }
    #endregion

    #region 序列
    public override bool IsSupportSequence() => false;

    public override string NextSequenceValueSeg(string name) => throw new NotSupportedException();
    #endregion

    #region PostDealEntityInfo
    protected override void PostDealEntityInfo(EntityInfo entityInfo)
    {
        //先矫正表名
        var schemaName = entityInfo.TableName.SchemaName.IfNullOrWhiteSpaceUse(entityInfo.TableName.DataBaseName);
        if (schemaName.IsNotNullOrWhiteSpace())
        {
            entityInfo.TableName.SetDataBaseName(schemaName);
            entityInfo.TableName.SetSchemaName(schemaName);
        }

        var cols = entityInfo.Props;
        #region 处理 [SqliteColumn]
        for (int i = 0; i < cols.Count; i++)
        {
            var entityPropertyInfo = cols[i];
            var propAttrs = entityPropertyInfo.PropertyInfo.GetCustomAttributes();

            //[SqliteColumn]
            var colAttr = propAttrs.FirstOrDefault(i => i is SqliteColumnAttribute) as SqliteColumnAttribute;
            if (colAttr != null)
            {
                //复写 [Column] 中的 Name, TypeName, Order, TimeFormat
                if (colAttr.Name.IsNotNullOrWhiteSpace())
                {
                    entityPropertyInfo.ColumnNamePure = colAttr.Name;
                }
                entityPropertyInfo.ColumnNameQuoted = AddQuote(entityPropertyInfo.ColumnNamePure);
                if (colAttr.TypeName.IsNotNullOrWhiteSpace())
                {
                    entityPropertyInfo.TypeName = colAttr.TypeName;
                }
                // [Column] 源码中默认就是-1
                entityPropertyInfo.Order = colAttr.Order;

                entityPropertyInfo.TimeFormat = colAttr.TimeFormat;
            }

            //根据 TypeName 判断 IsDbString
            if (entityPropertyInfo.TypeName.IsNotNullOrWhiteSpace())
            {
                entityPropertyInfo.IsDbString =
                    entityPropertyInfo.TypeName.Contains("char", StringComparison.OrdinalIgnoreCase)
                    || entityPropertyInfo.TypeName.Contains("text", StringComparison.OrdinalIgnoreCase)
                    || entityPropertyInfo.TypeName.Contains("clob", StringComparison.OrdinalIgnoreCase);
            }
            else
            {
                entityPropertyInfo.IsDbString = false;
            }

            //进一步处理 日期时间
            if (entityPropertyInfo.TimeFormat.IsNotNullOrWhiteSpace())
            {
                entityPropertyInfo.TimeFormat = colAttr.TimeFormat;
                entityPropertyInfo.IsDbString = true;
                entityPropertyInfo.ActiveTimeFormat = true;
            }
            else if (entityPropertyInfo.IsDateTime)
            {
                entityPropertyInfo.TimeFormat = Setting.DateTimeFormat;
                entityPropertyInfo.IsDbString = true;
                entityPropertyInfo.ActiveTimeFormat = true;
            }
            else if (entityPropertyInfo.IsDate)
            {
                entityPropertyInfo.TimeFormat = Setting.DateFormat;
                entityPropertyInfo.IsDbString = true;
                entityPropertyInfo.ActiveTimeFormat = true;
            }
            else if (entityPropertyInfo.IsTime)
            {
                entityPropertyInfo.TimeFormat = Setting.TimeFormat;
                entityPropertyInfo.IsDbString = true;
                entityPropertyInfo.ActiveTimeFormat = true;
            }
            else if (entityPropertyInfo.IsTimeSpan)
            {
                //TimeSpan 统一使用 整形或浮点型存储
                entityPropertyInfo.TimeFormat = null;
                entityPropertyInfo.IsDbString = false;
                entityPropertyInfo.ActiveTimeFormat = false;
            }
        }
        #endregion
    }
    #endregion

    public override string ConvertJsonVariableToSql(string seg, EnumJsonDataType srcType, EnumJsonAcceptAsType destType)
    {
        if (destType == EnumJsonAcceptAsType.DataTableValue) throw new Exception($"不可以将sql语句转为DataTable中的值!");
        //null
        if (srcType == EnumJsonDataType.Null) return seg;
        else if (srcType == EnumJsonDataType.Number)
        {
            //number
            //return cast(1 as json)
            if (destType == EnumJsonAcceptAsType.Doc) return $"cast({seg} as json)";
            //return 1
            else return seg;
        }
        if (srcType == EnumJsonDataType.String)
        {
            //string
            //return '"tom"'
            if (destType == EnumJsonAcceptAsType.Doc) return $"json_quote({seg})";
            //return 'tom'
            else return seg;
        }
        else if (srcType == EnumJsonDataType.Bool)
        {
            //true/false
            //return cast(true as json)
            if (destType == EnumJsonAcceptAsType.Doc) return $"cast({seg} as json)";
            //return true
            else return seg;
        }
        else
        {
            //arr/obj
            //不用区分
            return seg;
        }
    }

    public override string GetDefaultDbType(Type type)
    {
        var colType = base.GetDefaultDbType(type);
        if (colType.IsNotNullOrEmpty()) return colType;
        if (type.IsNullable()) type = Nullable.GetUnderlyingType(type);
        var code = type.GetTypeCode();
        colType = "json";
        switch (code)
        {
            case TypeCode.SByte:
            case TypeCode.Byte:
            case TypeCode.UInt16:
            case TypeCode.Int16:
            case TypeCode.Int32:
            case TypeCode.UInt32:
                colType = "int";
                break;
            case TypeCode.Int64:
            case TypeCode.UInt64:
                colType = "bigint";
                break;
            case TypeCode.Double:
            case TypeCode.Single:
            case TypeCode.Decimal:
                colType = "decimal(30,15)";
                break;
            case TypeCode.DateTime:
                colType = "datetime(6)";
                break;
            case TypeCode.String:
                colType = "text";
                break;
            default:
                break;
        }
        return colType;
    }

    #region Is系列: 判断 表/视图/列/存储过程/触发器 是否存在
    /// <inheritdoc />
    protected override string IsColumnExistInTableOrViewSql(string tableOrViewPureName, string columnPureName, string schemaPureName = null)
    {
        if (schemaPureName.IsNotNullOrEmpty())
        {
            // select 1
            // from otherdb.pragma_table_info('v_user')
            // where name = 'id';
            return
                $"""
                select 1 from {AddQuote(schemaPureName)}.pragma_table_info({AddQuote(tableOrViewPureName)})
                where name={ProtectStringToSeg(columnPureName)}
                """;
        }
        else
        {
            // select 1
            // from pragma_table_info('t_user')
            // where name = 'id';
            return
                $"""
                select 1 from pragma_table_info({AddQuote(tableOrViewPureName)})
                where name={ProtectStringToSeg(columnPureName)}
                """;
        }
    }
    /// <inheritdoc />
    protected override string IsTableOrViewExistSql(string tableOrViewPureName, string schemaPureName = null)
    {
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            return
                $"""
                select 1 from {AddQuote(schemaPureName)}.sqlite_master where (type='table' or type='view') and name={ProtectStringToSeg(tableOrViewPureName)}
                """;
        }
        else
        {
            return
                $"""
                select 1 from sqlite_master where (type='table' or type='view') and name={ProtectStringToSeg(tableOrViewPureName)}
                """;
        }
    }
    #endregion
}